package dao.impl;

import dao.UserDao;
import org.springframework.stereotype.Repository;
import pojo.User;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Repository
public class UserDaoImpl implements UserDao{

    @Resource(name="dataSource")
    private DataSource dataSource;


    @Override
    public int add(User user) {
        String sql = "insert into users(id,uname,sex,age,sal,birth) values(?,?,?,?,?,?)";
        PreparedStatement pst = null;
        int m = 0;
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            pst = conn.prepareStatement(sql);
            pst.setString(1, user.getId());
            pst.setString(2, user.getUname());
            pst.setBoolean(3, user.getSex());
            pst.setInt(4,user.getAge());
            pst.setDouble(5, user.getSal());
            pst.setDate(6, new java.sql.Date(user.getBirth().getTime()));
            m = pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            close(conn,pst,null);
        }
        return m;

    }

    @Override
    public int update(User user) {
        String sql = "update users set uname=?,sex=?,age=?,sal=?,birth=? where id=?";
        PreparedStatement pst = null;
        int m = 0;
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            pst = conn.prepareStatement(sql);
            pst.setString(1, user.getUname());
            pst.setBoolean(2, user.getSex());
            pst.setInt(3, user.getAge());
            pst.setDouble(4, user.getSal());
            pst.setDate(5, new java.sql.Date(user.getBirth().getTime()));
            pst.setString(6, user.getId());
            m = pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            close(conn,pst,null);
        }
        return m;
    }

    @Override
    public int delete(String id) {
        String sql = "delete from users where id=?";
        PreparedStatement pst = null;
        int m = 0;
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            pst = conn.prepareStatement(sql);
            pst.setString(1, id);
            m = pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            close(conn,pst,null);
        }
        return m;
    }

    @Override
    public List<User> findAll() {
        String sql = "select * from users";
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        List<User> users = new ArrayList<>();
        try {
            conn = dataSource.getConnection();
            pst = conn.prepareStatement(sql);
            rs = pst.executeQuery();
            while(rs.next()) {
                User user = new User();
                user.setId(rs.getString("id"));
                user.setUname(rs.getString("uname"));
                user.setAge(rs.getInt("age"));
                user.setSex(rs.getBoolean("sex"));
                user.setSal(rs.getDouble("sal"));
                user.setBirth(new Date(rs.getDate("birth").getTime()));
                users.add(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            close(conn,pst,rs);
        }
        return users;
    }

    private void close(Connection conn, PreparedStatement pst, ResultSet rs){
            try {
                if (conn!=null)
                    conn.close();
                if (pst!=null)
                    pst.close();
                if (rs!=null)
                    rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

    }
}
